﻿create table Course
(
	Id int identity primary key,
	Name varchar(100),
	Code varchar(100),
	[Description] varchar(2000)
);

create table CourseSelection
(
	Id int identity primary key,
	Student int,
	Course int,
	Trimester int,
	SelectDate datetime,
	Score int,
	Cancelled bit
);

create table Gender
(
	Id int identity primary key,
	Name varchar(100),
	Abbreviation varchar(100)
);

create table ScoreLevel
(
	Id int identity primary key,
	Name varchar(100),
	[Description] varchar(2000),
	LowBound int,
	UpBound int
);

create table Student
(
	Id int identity primary key,
	FirstName varchar(100),
	LastName varchar(100),
	Birthday datetime,
	Email varchar(100),
	DayPhone varchar(100),
	NightPhone varchar(100),
	[Address] varchar(100),
	StudentId varchar(100),
	Gender int,
	Picture image
);

create table Teacher
(
	Id int identity primary key,
	Trimester int,
	Course int,
	Tutor int
);

create table Trimester
(
	Id int identity primary key,
	Name varchar(100),
	Abbreviation varchar(100),
	StartDate date,
	EndDate date
);

create table Tutor
(
	Id int identity primary key,
	FirstName varchar(100),
	LastName varchar(100),
	Gender int,
	TutorLevel int
);

create table TutorLevel
(
	Id int identity primary key,
	Name varchar(100),
	Abbreviation varchar(100)		
);

CREATE TABLE [dbo].[ClassE](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BOOL] [bit] NULL,
	[INT] [int] NULL,
	[STRING] [varchar](100) NULL,
	[IMAGE] [image] NULL,
	[DATETIME] [datetime] NULL,
	[DECIMAL] [decimal](18, 2) NULL,
	[ClassDId] [int] NULL
);

CREATE TABLE [dbo].[ClassD](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BOOL] [bit] NULL,
	[INT] [int] NULL,
	[STRING] [varchar](100) NULL,
	[IMAGE] [image] NULL,
	[DATETIME] [datetime] NULL,
	[DECIMAL] [decimal](18, 2) NULL,
	[ClassEId] [int] NULL
);

CREATE TABLE [dbo].[ClassB](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BOOL] [bit] NULL,
	[INT] [int] NULL,
	[STRING] [varchar](100) NULL,
	[IMAGE] [image] NULL,
	[DATETIME] [datetime] NULL,
	[DECIMAL] [decimal](18, 2) NULL,
	[ClassBId] [int] NULL
);

CREATE TABLE [dbo].[ClassA](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BOOL] [bit] NULL,
	[INT] [int] NULL,
	[STRING] [varchar](100) NULL,
	[IMAGE] [image] NULL,
	[DATETIME] [datetime] NULL,
	[DECIMAL] [decimal](18, 2) NULL,
	[ClassBId] [int] NULL,
	[ClassBId2] [int] NULL,
	[ClassDId] [int] NULL,
	[SMALLINT] [smallint] NULL
);


go;


insert into Course(Name, Code, Description) values(
'Introduction to Computer Program Design', 
'COMP 102', 
'This course introduces the fundamentals of programming in a high-level programming language (Java), using an object oriented approach to program design. Students develop their programming skills by constructing computer programs for a variety of applications. The course provides a foundation for all later courses in computer science, and develops programming skills useful for students in many other disciplines.'
);
insert into Course(Name, Code, Description) values(
'Introduction to Data Structures and Algorithms', 
'COMP 103', 
'This course builds on COMP 102, focusing on the techniques for designing, building and analysing computer programs that deal with large collections of data. The course addresses techniques for programming with collections of data, and the data structures and algorithms needed to implement these collections. The course expands programming skills and provides an understanding of the principles of data abstraction, algorithm design, and the analysis of algorithms fundamental to computer science.'
);
insert into Course(Name, Code, Description) values(
'Introduction to Computer Science', 
'COMP 112', 
'This course introduces a range of important concepts and topics across Computer Science, Software Engineering and Network Engineering. Students will also gain a solid foundation of programming skills in object oriented programming. The course is an entry point to the BE(Hons) and BSc in Computer Science for students who already have basic programming skills.'
);
insert into Course(Name, Code, Description) values(
'Engineering Technology', 
'ENGR 101', 
'This course provides a general introduction to the fundamental technical concepts needed to understand the design and engineering of electronic, mechatronic, networked and software systems. Experience is gained in basic engineering practice, with assembly and testing of basic hardware, software and networked systems, and construction of a personal computer.'
);
insert into Course(Name, Code, Description) values(
'Engineering Modelling and Design', 
'ENGR 110', 
'This course introduces the role of modelling in the engineering design process. Different modelling techniques will be presented and techniques for evaluating each that can aid design decisions will be demonstrated. Practical work will support the learning of different modelling and simulation techniques.'
);
insert into Course(Name, Code, Description) values(
'Algorithms and Data Structures', 
'COMP 261', 
'This course covers a range of algorithms and data structures building on the fundamental structures and algorithms from COMP 103. The major areas covered are: graph algorithms, graphics algorithms and advanced data structures. This course takes a practical approach focusing on the implementation of a wide variety of algorithms.'
);
insert into Course(Name, Code, Description) values(
'Data Acquisition', 
'ECEN 201', 
'The course provides an introduction into the acquisition and analysis of engineering and scientific data and signals by means of data acquisition systems. Topics covered include the principle of operation and characteristics of sensors, signal conditioning, analogue to digital and digital to analogue conversion, sampling, I/O techniques and databases, the interfacing of both personal computers and microcontrollers using appropriate software.'
);
insert into Course(Name, Code, Description) values(
'Digital Electronics', 
'ECEN 202', 
'A practically oriented introduction to the design and construction of digital electronic instruments. The course provides a foundation in binary arithmetic and Boolean algebra, logic gates and families, combinational and sequential logic design, programmable logic and digital-to-analogue conversion.'
);
insert into Course(Name, Code, Description) values(
'Analogue Circuits and Systems', 
'ECEN 203', 
'This course is a practically oriented introduction to analogue electronics. Topics covered include circuit theorems, transducers, power supplies, transistor amplifiers, operational amplifiers and circuits.'
);
insert into Course(Name, Code, Description) values(
'Signals and Systems', 
'ECEN 220', 
'The course introduces analysis techniques for signals and linear time-invariant systems as well as fundamentals of probability, statistics and random variables. It includes Laplace and Fourier transform techniques, with applications to circuit analysis, electromechanical systems and communication theory. The course also introduces concepts of probability density, functions of random variables and statistical tests.'
);
insert into Course(Name, Code, Description) values(
'Work Experience Preparation', 
'ENGR 291', 
'A range of activities in preparation for the work experience requirement for the BE(Hons).'
);
insert into Course(Name, Code, Description) values(
'Systems Programming', 
'NWEN 241', 
'This course considers the issues raised when programming at a low-level. for example in embedded systems, OS system level, or network protocol stacks. Topics include: an introduction to C and C++ language programming; and higher- level systems programming using scripting languages. It will include motivating examples related to a wide variety of applications of system programming.'
);
insert into Course(Name, Code, Description) values(
'Computer Organisation', 
'NWEN 242', 
'The course develops an understanding of the structure of computers and how they are to execute programmes. The course introduces the fundamentals of assembly language programming, data representation and computer arithmetic. It then develops an understanding of microprocessor architecture at the hardware level. Topics include digital electronics, arithmetic and logic unit (ALU), data paths, pipelining, memory hierarchy, I/O and motivating examples of computer systems. Students are recommended, but not required, to have some familiarity with C programming prior to taking this course.'
);
insert into Course(Name, Code, Description) values(
'Network Applications', 
'NWEN 243', 
'The course provides a broad introduction to computer networks and a basic understanding of network application programming, with an emphasis on the working principles and application of computer networks. It covers a range of topics including basic data communication and computer network concepts, protocols, networked computing concepts and principles, network applications development and network security. The course features an interactive laboratory component with projects examining modern networking technologies such as, GPS enabled mobile phone applications, multimedia and distributed applications. Students are recommended, but not required, to have some familiarity with C programming prior to taking this course.'
);
insert into Course(Name, Code, Description) values(
'Software Development', 
'SWEN 221', 
'This course develops a deeper understanding of object- oriented programming and associated practices. The focus is on programming techniques at the micro scale. Topics include: inheritance, polymorphism, genericity, error handling, testing and debugging. A sequence of short assignments will develop the key ideas and practices; rigour in testing will be developed through (automated) assessment of programme correctness.'
);
insert into Course(Name, Code, Description) values(
'Software Design', 
'SWEN 222', 
'This course develops a deeper understanding of object- oriented design. In particular, students will be expected to design and implement several large programs from scratch by working together in groups. Thefocus of this course is programming techniques at the macro scale.'
);
insert into Course(Name, Code, Description) values(
'Software Engineering Analysis', 
'SWEN 223', 
'This course provides a working knowledge of analysis and design methods found in modern software engineering systems. Topics include the analysis tools and techniques of the software engineer, the process of analysis and object- oriented analysis and design. Students will complete project work using UML and OCL, using modern Computer Aided Software Engineering (CASE) toolsets.'
);
insert into Course(Name, Code, Description) values(
'Formal Foundations of Programming', 
'SWEN 224', 
'This course introduces a selection of topics, focusing on the use of formal notations and formal models in the specification, design and analysis and engineering of programs, languages and machines. Topics covered will include finite state machines, formal languages and grammars, operational semantics, and specifying and analysing systems using software tools for formal specification.'
);
--insert into Course values(
--'', 
--'', 
--''
--);

insert into Gender(Name, Abbreviation) values('Male', 'M');
insert into Gender(Name, Abbreviation) values('Female', 'F');
insert into Gender(Name, Abbreviation) values('Transgender', 'T');

insert into ScoreLevel(Name, Description, LowBound, UpBound) values('A+', 'Excellent+', 91, 99);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('A', 'Excellent', 81, 90);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('A-', 'Excellent-', 71, 80);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('B+', 'Good+', 66, 70);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('B', 'Good', 61, 65);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('B-', 'Good-', 56, 60);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('C+', 'Satisfied+', 51, 55);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('C', 'Satisfied', 50, 50);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('C-', 'Satisfied-', 31, 49);
insert into ScoreLevel(Name, Description, LowBound, UpBound) values('D', 'Common', 1, 30);

insert into Trimester(Name, Abbreviation, StartDate, EndDate) values('2014 T1', '2014T1', '2014-02-01','2014-06-30');
insert into Trimester(Name, Abbreviation, StartDate, EndDate) values('2014 T2', '2014T2', '2014-07-01','2014-10-31');
insert into Trimester(Name, Abbreviation, StartDate, EndDate) values('2014 T3', '2014T3', '2014-11-20','2015-01-20');

insert into TutorLevel(Name, Abbreviation) values ('Tutor', 'Tutor');
insert into TutorLevel(Name, Abbreviation) values ('Teacher', 'Teacher');
insert into TutorLevel(Name, Abbreviation) values ('Director', 'Director');
insert into TutorLevel(Name, Abbreviation) values ('Doctor', 'Doctor');
insert into TutorLevel(Name, Abbreviation) values ('Professor', 'Professor');

insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Dominick', 'Johnson', '1990-01-01', 'Johnson.Dominick@gmail.com', '021 1234567', '021 1234567', 'Onehunga, Auckland', '300123001', 1);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Ronie', 'Colemen', '1990-02-01', 'Ronie.Colemen@gmail.com', '021 2345678', '021 2345678', 'Epsom, Auckland', '300123002', 1);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Bill', 'Henderson', '1990-03-01', 'Bill.Henderson@gmail.com', '021 3456789', '021 3456789', 'Roshill, Auckland', '300123003', 1);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Taylor', 'Fenn', '1990-04-01', 'Taylor.Fenn@gmail.com', '027 9876543', '027 9876543', 'Karori, Wellington', '300123004', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Alice', 'Arroyo', '1990-03-10', 'Alice.Arroyo@gmail.com', '027 8765432', '027 8765432', 'Kelburn, Wellington', '300123005', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Mary', 'Bangert', '1990-04-10', 'Mary.Bangert@gmail.com', '022 1472589', '022 1472589', 'Newtown, Wellington', '300123006', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Joey', 'Tribiani', '1989-07-21', 'Joey.Tribiani@gmail.com', '022 9635551', '022 9635551', 'Thordon, Wellington', '300123007', 1);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Pheebe', 'Buffey', '1991-08-17', 'Pheebe.Buffey@gmail.com', '021 8521599', '021 8521599', 'Karori, Wellington', '300123008', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Ross', 'Geller', '1988-01-21', 'Ross.Geller@gmail.com', '027 1122334', '027 1122334', 'Onehunga, Auckland', '300123009', 1);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Rachal', 'White', '1990-11-11', 'Rachal.White@gmail.com', '022 9988776', '022 9988776', 'Onehunga, Auckland', '300123010', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Monica', 'Geller', '1990-05-28', 'Monica.Geller@gmail.com', '021 7788991', '021 7788991', 'Roshill, Auckland', '300123011', 2);
insert into Student(FirstName, LastName, Birthday, Email, DayPhone, NightPhone, Address, StudentId, Gender) values('Chandler', 'Black', '1988-02-22', 'Chandler.Black@gmail.com', '025 1357924', '025 1357924', 'Epsom, Auckland', '300123012', 1);
--insert into Student values('', '', '', '', '', '', '', '', 1);

insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Mike', 'Wilson', 1, 5);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('David', 'Harper', 1, 5);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Susan', 'Schenk', 2, 1);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Vivian', 'Wei', 2, 1);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Johan', 'Barnard', 1, 2);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Anna', 'Cramp', 2, 4);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('John', 'Hine', 1, 2);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Mengjie', 'Zhang', 1, 4);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Keith', 'Willett', 1, 5);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Lissa', 'Harrop', 2, 3);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Libby', 'Kelly', 1, 5);
insert into Tutor(FirstName, LastName, Gender, TutorLevel) values('Suzan', 'Hall', 2, 3);

insert into Teacher(Trimester, Course, Tutor) values(1, 1, 2);
insert into Teacher(Trimester, Course, Tutor) values(1, 2, 3);
insert into Teacher(Trimester, Course, Tutor) values(1, 3, 4);
insert into Teacher(Trimester, Course, Tutor) values(1, 4, 2);
insert into Teacher(Trimester, Course, Tutor) values(1, 5, 5);
insert into Teacher(Trimester, Course, Tutor) values(1, 6, 8);
insert into Teacher(Trimester, Course, Tutor) values(1, 7, 12);
insert into Teacher(Trimester, Course, Tutor) values(1, 8, 11);
insert into Teacher(Trimester, Course, Tutor) values(1, 9, 6);
insert into Teacher(Trimester, Course, Tutor) values(1, 10, 9);
insert into Teacher(Trimester, Course, Tutor) values(1, 11, 10);
insert into Teacher(Trimester, Course, Tutor) values(1, 12, 7);
insert into Teacher(Trimester, Course, Tutor) values(1, 13, 1);
insert into Teacher(Trimester, Course, Tutor) values(1, 14, 6);
insert into Teacher(Trimester, Course, Tutor) values(1, 15, 9);
insert into Teacher(Trimester, Course, Tutor) values(1, 16, 12);
insert into Teacher(Trimester, Course, Tutor) values(1, 17, 10);
insert into Teacher(Trimester, Course, Tutor) values(1, 18, 2);

insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(1, 1, 1, '2013-01-01', 3, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(1, 2, 1, '2013-01-01', null, 1);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(1, 3, 1, '2013-01-01', 3, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(2, 1, 1, '2013-01-02', 2, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(2, 2, 1, '2013-01-02', 1, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(3, 4, 1, '2013-01-01', 5, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(3, 5, 1, '2013-01-01', 5, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(3, 6, 1, '2013-01-01', null, 1);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(4, 5, 1, '2013-01-03', 4, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(4, 6, 1, '2013-01-04', 6, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(6, 7, 1, '2013-01-01', 2, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(7, 7, 1, '2013-01-02', 6, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(7, 8, 1, '2013-01-03', 7, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(8, 8, 1, '2013-01-01', null, 1);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(9, 2, 1, '2013-01-02', 3, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(9, 3, 1, '2013-01-02', 5, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(10, 8, 1, '2013-01-03', 8, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(10, 9, 1, '2013-01-03', 5, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(10, 10, 1, '2013-01-04', 4, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(11, 11, 1, '2013-01-01', 6, 0);
insert into CourseSelection(Student, Course, Trimester, SelectDate, Score, Cancelled) values(11, 12, 1, '2013-01-01', 3, 0);



insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,11,'B1', '2013-01-01', 1.01, NULL);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,12,'B2', '2013-01-01', 2.01, 1);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,13,'B3', '2013-01-01', 3.01, 2);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,14,'B4', '2013-01-01', 4.01, 3);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,15,'B5', '2013-01-01', 5.01, 4);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,16,'BB1', '2013-01-01', 6.01, NULL);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,17,'BB2', '2013-01-01', 7.01, 6);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,18,'BB3', '2013-01-01', 8.01, 7);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,19,'BB4', '2013-01-01', 9.01, 8);
insert into ClassB([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID) VALUES(1,20,'BB5', '2013-01-01', 10.01, 9);




insert into ClassA([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID, ClassBId2, ClassDId) VALUES(1,10,'A', '2013-01-01', 10.01, 1,6, NULL);
insert into ClassA([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID, ClassBId2, ClassDId) VALUES(0,20,'BB', '2014-01-01', 20.01, NULL,NULL, NULL);
insert into ClassA([BOOL], [INT], [STRING], [DATETIME], [DECIMAL], CLASSBID, ClassBId2, ClassDId) VALUES(0,20,'CC', '2014-01-01', 20.01, 1,NULL, NULL);

